package com.power.sifa.approve.dao.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.plf.common.utils.StringUtil;
import com.plf.core.common.dao.OffersetTransformers;
import com.power.sifa.approve.dao.Apl_admreviewDaoCustom;
import com.power.sifa.approve.model.Apl_admreview;
import com.power.sifa.approve.model.Apl_arrive_main;
import com.power.sifa.approve.model.pageInfo.Apl_admreviewPageInfo;
import com.power.sifa.statistics.model.pageInfo.StatisticsInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.query.internal.NativeQueryImpl;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * 行政复议申请 持久层实现(hibernate)
 */

@Slf4j
@Repository
public class Apl_admreviewDaoImpl implements Apl_admreviewDaoCustom {

    private final EntityManager entityManager;

    public Apl_admreviewDaoImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public List<Apl_admreview> findList(Apl_admreviewPageInfo pageInfo) {
        List<Apl_admreview> result = new ArrayList<>();

        StringBuffer sql = new StringBuffer("select A.*, B.applycode,B.regusername,B.createtime, B.id as applyid, B" +
                ".acceptcode, B.source as source, B.status as mainstatus from  Apl_admreview A " +
                " inner join apl_main B ON A.id = B.id where 1 = 1 ");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;
        // 由申请流水号调整为案号查询
        if (StringUtils.isNotEmpty(pageInfo.getF_acceptcode())) {
            sql.append(" and B.acceptcode like ?" + index++);
            paramsList.add("%" + pageInfo.getF_acceptcode() + "%");
        }
        if (StringUtils.isNotEmpty(pageInfo.getF_applyname())) {
            sql.append("and A.applyname like ?" + index++);
            paramsList.add("%" + pageInfo.getF_applyname() + "%");
        }
        if (StringUtils.isNotEmpty(pageInfo.getF_targetname())) {
            sql.append(" and A.targetname like ?" + index++);
            paramsList.add("%" + pageInfo.getF_targetname() + "%");
        }
        if (StringUtils.isNotEmpty(pageInfo.getF_regusername())) {
            sql.append(" and B.regusername like ?" + index++);
            paramsList.add("%" + pageInfo.getF_regusername() + "%");
        }
        if (ObjectUtil.isNotEmpty(pageInfo.getF_applystarttime())) {
            sql.append(" and B.createtime >= ?" + index++);
            paramsList.add(pageInfo.getF_applystarttime());
        }
        if (ObjectUtil.isNotEmpty(pageInfo.getF_applyendtime())) {
            sql.append(" and B.createtime <= ?" + index++);
            paramsList.add(pageInfo.getF_applyendtime());
        }
        // 行政复议状态 f_status
        if (ObjectUtil.isNotNull(pageInfo.getF_status())) {
            sql.append(" and A.status in ?" + index++);
            List<String> statusList = Arrays.asList(pageInfo.getF_status().split(","));
            paramsList.add(statusList);
        }

        if (!StringUtil.isEmpty(pageInfo.getReguserid())) {
            sql.append(" and B.reguserid = ?" + index++);
            paramsList.add(pageInfo.getReguserid());
        }

        // 状态
        if (!CollectionUtil.isEmpty(pageInfo.getF_mainstatus())) {
            sql.append(" and B.status in ?" + index++);
            paramsList.add(pageInfo.getF_mainstatus());
        }

        // 排序
        sql.append(" order by B." + pageInfo.getSort() + " " + pageInfo.getOrder());
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        log.info(sql.toString());

        // 添加条件
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        // 查询总数
        pageInfo.setAllcount(query.getResultList().size());
        // 分页
        if (pageInfo.getAllcount() > 0) {
            int pageStart = (pageInfo.getPageNumber() - 1) * pageInfo.getPageSize();
            List<Map> list = query.setFirstResult(pageStart).setMaxResults(pageInfo.getPageSize()).getResultList();
            if (CollectionUtil.isNotEmpty(list)) {
                result = JSON.parseArray(JSON.toJSONString(list), Apl_admreview.class);
            }
        }
        return result;
    }

    /**
     * 归档阶段的数据分页查询
     *
     * @param pageInfo
     * @return
     */
    @Override
    public List<Apl_admreview> queryArchiveData(Apl_admreviewPageInfo pageInfo) {
        List<Apl_admreview> result = new ArrayList<>();
        // 按照对应状态进行区分，已完结状态优先级排序在前
        StringBuffer sql = new StringBuffer(" SELECT A .ID AS applyid, A.applycode AS applycode, A.acceptcode AS acceptcode, B.targetname, A.instid, A.SOURCE, B.applyname, B.reason, B.prtype, B.decisiontype, B.hearfirstid, B.hearfirstname, B.hearsecondid, B.hearsecondname, B.acceptfirstid, B.acceptfirstname, B.acceptsecondid, B.acceptsecondname, A.finishtime AS decisiontime, A.archivetime AS archivetime, B.decisiondate, B.proctype, A.subtime AS createtime, B.status, CASE WHEN B.status IN( 6002909, 6002906, 6002905, 6002907) THEN 1 ELSE 2 END status_order FROM Apl_main A INNER JOIN Apl_admreview b ON A.ID = B.ID WHERE 1 = 1 ");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;
        // 案号查询
        if (StringUtils.isNotEmpty(pageInfo.getF_acceptcode())) {
            sql.append(" and A.acceptcode like ?" + index++);
            paramsList.add("%" + pageInfo.getF_acceptcode() + "%");
        }
        // 申请人查询
        if (StringUtils.isNotEmpty(pageInfo.getF_applyname())) {
            sql.append(" and B.applyname like ?" + index++);
            paramsList.add("%" + pageInfo.getF_applyname() + "%");
        }
        // 被申请人查询
        if (StringUtils.isNotEmpty(pageInfo.getF_targetname())) {
            sql.append(" and B.targetname like ?" + index++);
            paramsList.add("%" + pageInfo.getF_targetname() + "%");
        }
        // 行政行为类型
        if (ObjectUtil.isNotEmpty(pageInfo.getF_prtype())) {
            sql.append(" and B.prtype = ?" + index++);
            paramsList.add(pageInfo.getF_prtype());
        }
        // 申请来源
        if (ObjectUtil.isNotEmpty(pageInfo.getF_source())) {
            sql.append(" and A.source = ?" + index++);
            paramsList.add(pageInfo.getF_source());
        }

        // 申请时间
        if (ObjectUtil.isNotEmpty(pageInfo.getF_applystarttime())) {
            sql.append(" and A.subtime >= ?" + index++);
            paramsList.add(pageInfo.getF_applystarttime());
        }
        if (ObjectUtil.isNotEmpty(pageInfo.getF_applyendtime())) {
            sql.append(" and A.subtime <= ?" + index++);
            paramsList.add(pageInfo.getF_applyendtime());
        }

        // 行政复议状态 f_status
        if (ObjectUtil.isNotNull(pageInfo.getF_status())) {
            sql.append(" and B.status in ?" + index++);
            List<String> statusList = Arrays.asList(pageInfo.getF_status().toString().split(","));
            paramsList.add(statusList);
        }
        // 排序
        sql.append(" order by subtime desc, " + pageInfo.getSort() + " " + pageInfo.getOrder());
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        log.info(sql.toString());

        // 添加条件
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        // 查询总数
        pageInfo.setAllcount(query.getResultList().size());
        // 分页
        if (pageInfo.getAllcount() > 0) {
            int pageStart = (pageInfo.getPageNumber() - 1) * pageInfo.getPageSize();
            List<Map> list = query.setFirstResult(pageStart).setMaxResults(pageInfo.getPageSize()).getResultList();
            if (CollectionUtil.isNotEmpty(list)) {
                result = JSON.parseArray(JSON.toJSONString(list), Apl_admreview.class);
            }
        }
        return result;
    }

    /**
     * 大屏统计-统计审理中的状态
     *
     * @param info
     * @return
     */
    @Override
    public Long countPorcessingCaseStatus(StatisticsInfo info) {
        StringBuffer sql = new StringBuffer("select count(*) from apl_main am where 1 = 1 and matid = '100000_360100-000201009000-QT-002-02' ");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;

        // 判断地区是否存在，不存在则查询所有，存在则查询地区
        if (StringUtils.isNotEmpty(info.getAcceptadid())) {
            sql.append(" and am.acceptadid = ?" + index++);
            paramsList.add(info.getAcceptadid());
        }

        // 状态
        if (StringUtils.isNotEmpty(String.valueOf(info.getStatus()))) {
            sql.append(" and am.status in ?" + index++);
            List status = info.getStatus();
            paramsList.add(status);
        }

        // 年份
        if (StringUtils.isNotEmpty(info.getYear())) {
            sql.append(" and substring(am.subtime, 1, 4) = ?" + index++);
            paramsList.add(info.getYear());
        }

        Query query = entityManager.createNativeQuery(sql.toString());
        // 添加条件
        for (int i = 0; i < paramsList.size(); i++) {
            log.info("第{}个参数:{}", i + 1, paramsList.get(i));
            query.setParameter(i + 1, paramsList.get(i));
        }
        log.info(sql.toString());

        try {
            Number result = (Number) query.getSingleResult();
            Long count = (result != null) ? result.longValue() : 0L;
            return count;
        } catch (Exception e) {
            log.error("查询没有返回结果");
            return 0L; // 或者其他适当的默认值
        }
    }


    /**
     * 大屏统计-统计已审结的状态
     *
     * @param info
     * @return
     */
    @Override
    public Long countEndedCaseStatus(StatisticsInfo info) {
        StringBuffer sql = new StringBuffer("select count(*) from apl_main am where 1 = 1 ");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;

        // 判断地区是否存在，不存在则查询所有，存在则查询地区
        if (StringUtils.isNotEmpty(info.getAcceptadid())) {
            sql.append(" and am.acceptadid = ?" + index++);
            paramsList.add(info.getAcceptadid());
        }

        // 状态
        if (StringUtils.isNotEmpty(String.valueOf(info.getStatus()))) {
            sql.append(" and am.status in ?" + index++);
            List status = info.getStatus();
            paramsList.add(status);
        }

        Query query = entityManager.createNativeQuery(sql.toString());
        // 添加条件
        for (int i = 0; i < paramsList.size(); i++) {
            log.info("第{}个参数:{}", i + 1, paramsList.get(i));
            query.setParameter(i + 1, paramsList.get(i));
        }
        log.info(sql.toString());

        try {
            Number result = (Number) query.getSingleResult();
            Long count = (result != null) ? result.longValue() : 0L;
            return count;
        } catch (Exception e) {
            log.error("查询没有返回结果");
            return 0L; // 或者其他适当的默认值
        }
    }

    /**
     * 大屏统计-统计中止状态
     *
     * @param info
     * @return
     */
    @Override
    public Long countSuspendCaseStatus(StatisticsInfo info) {
        StringBuffer sql = new StringBuffer("select count(*) from apl_main am left join apl_admreview aa on am.id = aa.id where 1 = 1 ");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;

        // 判断地区是否存在，不存在则查询所有，存在则查询地区
        if (StringUtils.isNotEmpty(info.getAcceptadid())) {
            sql.append(" and am.acceptadid = ?" + index++);
            paramsList.add(info.getAcceptadid());
        }

        // 状态
        if (StringUtils.isNotEmpty(String.valueOf(info.getStatus()))) {
            sql.append("  and aa.isrest = ?" + index++);
            int status = Integer.parseInt(info.getStatus().get(0).toString());
            paramsList.add(status);
        }

        Query query = entityManager.createNativeQuery(sql.toString());
        // 添加条件
        for (int i = 0; i < paramsList.size(); i++) {
            log.info("第{}个参数:{}", i + 1, paramsList.get(i));
            query.setParameter(i + 1, paramsList.get(i));
        }
        log.info(sql.toString());

        try {
            Number result = (Number) query.getSingleResult();
            Long count = (result != null) ? result.longValue() : 0L;
            return count;
        } catch (Exception e) {
            log.error("查询没有返回结果");
            return 0L; // 或者其他适当的默认值
        }
    }

    /**
     * 行政复议分页查询送达记录
     *
     * @param pageInfo
     * @return
     */
    @Override
    public List<Apl_arrive_main> queryArriveData(Apl_admreviewPageInfo pageInfo) {
        List<Apl_arrive_main> result = new ArrayList<>();

        //  排除未提交的状态
        StringBuffer sql = new StringBuffer("select A.*, B.acceptcode, B.source, C.tosendnum,C.warntime,D.sendednum from apl_admreview A left join apl_main B ON A.id = B.id \n" +
                "left join (select count(id) as toSendNum,applyid,min(warntime) as warntime, sendstatus from Apl_arrive_detail GROUP BY applyid,sendstatus HAVING sendstatus = 3201) C ON A.id = C.applyid\n" +
                "left join (select count(id) as sendedNum,applyid,min(warntime) as warntime, sendstatus from Apl_arrive_detail GROUP BY applyid,sendstatus HAVING sendstatus = 3200) D ON A.id = D.applyid\n" +
                "where 1=1 AND B.status != 6000600 ");

        List<Object> paramsList = new ArrayList<>();
        int index = 1;
        // 案号查询
        if (StringUtils.isNotEmpty(pageInfo.getF_acceptcode())) {
            sql.append(" and B.acceptcode like ?" + index++);
            paramsList.add("%" + pageInfo.getF_acceptcode() + "%");
        }
        // 流程matid查询
        if (StringUtils.isNotEmpty(pageInfo.getF_matid())) {
            sql.append(" and B.matid = ?" + index++);
            paramsList.add(pageInfo.getF_matid() );
        }
        // 申请人查询
        if (StringUtils.isNotEmpty(pageInfo.getF_applyname())) {
            sql.append(" and A.applyname like ?" + index++);
            paramsList.add("%" + pageInfo.getF_applyname() + "%");
        }
        // 被申请人查询
        if (StringUtils.isNotEmpty(pageInfo.getF_targetname())) {
            sql.append(" and A.targetname like ?" + index++);
            paramsList.add("%" + pageInfo.getF_targetname() + "%");
        }
        // 行政行为类型
        if (ObjectUtil.isNotEmpty(pageInfo.getF_prtype())) {
            sql.append(" and A.prtype = ?" + index++);
            paramsList.add(pageInfo.getF_prtype());
        }
        // 申请来源
        if (ObjectUtil.isNotEmpty(pageInfo.getF_source())) {
            sql.append(" and B.source = ?" + index++);
            paramsList.add(pageInfo.getF_source());
        }

        if (StringUtils.isNotEmpty(pageInfo.getF_applystarttime())) {
            sql.append(" and C.warntime >= ?" + index++);
            paramsList.add(pageInfo.getF_applystarttime());
        }
        if (StringUtils.isNotEmpty(pageInfo.getF_applyendtime())) {
            sql.append(" and C.warntime <= ?" + index++);
            paramsList.add(pageInfo.getF_applyendtime());
        }

        // 行政复议状态 f_status
        if (ObjectUtil.isNotNull(pageInfo.getF_status())) {
            sql.append(" and B.status in ?" + index++);
            List<String> statusList = Arrays.asList(pageInfo.getF_status().toString().split(","));
            paramsList.add(statusList);
        }

        // 排序
        sql.append(" order by warntime asc, B.createtime desc ");
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        log.info(sql.toString());

        // 添加条件
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        // 查询总数
        pageInfo.setAllcount(query.getResultList().size());
        // 分页
        if (pageInfo.getAllcount() > 0) {
            int pageStart = (pageInfo.getPageNumber() - 1) * pageInfo.getPageSize();
            List<Map> list = query.setFirstResult(pageStart).setMaxResults(pageInfo.getPageSize()).getResultList();
            if (CollectionUtil.isNotEmpty(list)) {
                result = JSON.parseArray(JSON.toJSONString(list), Apl_arrive_main.class);
            }
        }
        return result;
    }

}
